package hk;



import com.entity1.MyUser;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class JdbcUtil {
    static Connection conn = null;
    static PreparedStatement ps = null;
    static ResultSet rs = null;

    public static void getConn() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/hk?characterEncoding=utf8&useSSL=false","root","000000");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//    public MyUser getUserByNameAndPsw(String username, String passward) throws ClassNotFoundException,SQLException{
//        Class.forName("com.mysql.jdbc.Driver");
//        Connection conn=null;
//        PreparedStatement ps=null;
//        ResultSet rs=null;
//        conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/hk?characterEncoding=utf8&useSSL=false","root","000000");
//
//        String sql=" select id,username,age,passward,address from entity where username=? and passward=?";
//        ps=conn.prepareStatement(sql);
//        ps.setString(1,username);
//        ps.setString(2,passward);
//
//        rs=ps.executeQuery();
//
//        while (rs.next()){
//            MyUser entity=new MyUser();
//            entity.setId(rs.getInt(1));
//            entity.setName(rs.getString(2));
//            entity.setPassward(rs.getInt(3));
//            entity.setAge(rs.getInt(4));
//            entity.setAddress(rs.getString(5));
//            return entity;
//        }
//
//
//        rs.close();
//        conn.close();
//        ps.close();
//        return null;
//    }

    public static MyUser findUser(String username) throws ClassNotFoundException,SQLException{

//        Class.forName("com.mysql.jdbc.Driver");
//        Connection connection = null;
//        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//
//        connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hk?characterEncoding=utf8&useSSL=false","root","000000");
        getConn();
        String sql=" select id,username,age,passward,address from user where username=?";

        ps=conn.prepareStatement(sql);
        //System.out.println("username="+username);
        ps.setString(1,username);
        rs=ps.executeQuery();


        while (rs.next()){
            MyUser myUser=new MyUser();
            myUser.setId(rs.getInt(1));
            myUser.setUsername(rs.getString(2));
            myUser.setPassward(rs.getInt(3));
            myUser.setAge(rs.getInt(4));
            myUser.setAddress(rs.getString(5));
            //System.out.println(myUser);
            return myUser;
        }
        rs.close();
        conn.close();
       ps.close();
        return null;
    }

    public static int addUser(int id,String username,int age,int passward,String address) throws Exception{

//        Class.forName("com.mysql.jdbc.Driver");
//        Connection connection = null;
//        PreparedStatement preparedStatement = null;
//
//        connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hk?characterEncoding=utf8&useSSL=false","root","000000");
        getConn();
        String sql = "insert into user(id,username,age,passward,address) VALUES (?,?,?,?,?)";
        ps=conn.prepareStatement(sql);

        ps.setInt(1,id);
        ps.setString(2,username);
        ps.setInt(3,age);
        ps.setInt(4,passward);
        ps.setString(5,address);


        //执行的结果
        int result = ps.executeUpdate();



        conn.close();
        ps.close();
        System.out.println(result);
        return result;
    }

        public static List<MyUser> queryAll() throws Exception{
            getConn();
            String sql="select * from user";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            List<MyUser> list=new ArrayList<>();//定义一个集合来接收
            while ((rs.next())){
               MyUser myUser=new MyUser();
               myUser.setId(rs.getInt(1));
               myUser.setUsername(rs.getString(2));
               myUser.setPassward(rs.getInt(3));
               myUser.setAge(rs.getInt(4));
               myUser.setAddress(rs.getString(5));
               list.add(myUser);//向集合中添加MyUser对象
            }
            rs.close();
            conn.close();
            ps.close();
            return list;
        }
    public static int delUser(int id) throws Exception{
        getConn();
        String sql="delete from user where id=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1,id);
        int result = ps.executeUpdate();
        conn.close();
        rs.close();
        ps.close();
        System.out.println(result);
        return result;

        }
//    public static void close(){
//
//        try {
//            conn.close();
//
////            rs.close();
//            ps.close();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }
//    public static void main(String[] args) throws Exception{
//        JdbcUtil jdbcUtil=new JdbcUtil();
//       // MyUser entity=jdbcUtil.findUser("a1");
//        int i = jdbcUtil.addUser(6, "a6", 23, 3333, "上海");
//        System.out.println("i="+i);
//    }
}
